package net.zjitc.dao.impl;

import net.zjitc.dao.ProductDao;
import net.zjitc.pojo.CategorySecond;
import net.zjitc.pojo.Evaluate;
import net.zjitc.pojo.Product;
import net.zjitc.pojo.User;
import net.zjitc.utils.PageBean;
import net.zjitc.utils.db.C3P0Utils;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by Administrator on 2017/11/28.
 */
public class ProductDaoImpl implements ProductDao {
    @Override
    public PageBean<Product> findPageBean(int currentPage, int pageSize) {
        PageBean<Product> pageBean=new PageBean<Product>();
        pageBean.setPage(currentPage);
        pageBean.setPageSize(pageSize);
        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource());
        String sql="select * from product order by pid limit ?,?";
        String countSql="select count(*) from product";
        try {
            List<Product> list= (List<Product>) runner.query(sql,new BeanListHandler(Product.class),(currentPage-1)*pageSize,pageSize);
            pageBean.setPageList(list);
            Long c= (Long) runner.query(countSql,new ScalarHandler());
            int count=c.intValue();
            pageBean.setCount(count);
            pageBean.getPageCount();

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return pageBean;
    }

    @Override
    public int insert(Product product) {
        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource());
        String sql="insert into product(pname,market_price,shop_price,image,pdesc,is_hot,pdate,csid) value(?,?,?,?,?,?,?,?)";


        try {
            int  count = runner.update(sql,product.getPname(),product.getMarket_price(),product.getShop_price(),product.getImage(),product.getPdesc(),product.getIs_hot(),product.getPdate(),product.getCategorySecond().getCsid());
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return 0;
    }

    @Override
    public Product findByPid(String pid) {
        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource());
        String sql="select * from product where pid=?";
        try {
            Map<String,Object> map=runner.query(sql,new MapHandler(),pid);
            CategorySecond categorySecond=new CategorySecond();
            Product product=new Product();
            BeanUtils.populate(categorySecond,map);
            BeanUtils.populate(product,map);
            product.setCategorySecond(categorySecond);
            return product;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int update(Product product) {
        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource());
        String sql="update product set pname=?,market_price=?,shop_price=?,image=?,pdesc=?,is_hot=?,pdate=?,csid=? where pid=?";
        try {
            int count=runner.update(sql,product.getPname(),product.getMarket_price(),product.getShop_price(),product.getImage(),product.getPdesc(),product.getIs_hot(),product.getPdate(),product.getCategorySecond().getCsid(),product.getPid());
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int delete(int pid) {
        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource());
        String sql="delete from product where pid = ?";
        try {
            runner.update(sql,pid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }



    @Override
    public List<Product> findAllIsHot(String isHot) {
        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource());
        String sql="select * from product where is_hot=1";
        try {
            List<Product> list= (List<Product>) runner.query(sql,new BeanListHandler(Product.class));
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List findAllDate() {
        QueryRunner queryRunner=new QueryRunner(C3P0Utils.getDataSource());
        String sql="select * from product";
        List<Product> list=null;
        try {
            list= queryRunner.query(sql,new BeanListHandler<Product>(Product.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public PageBean<Product> findPageBeanByCid(int page, int pageSize, int cid) {
        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource());
        PageBean<Product> bean=new PageBean<Product>();
        bean.setPage(page);
        bean.setPageSize(pageSize);
        String sqlCount="SELECT COUNT(*) FROM product p JOIN category c , categorysecond cs WHERE p.csid=cs.csid AND cs.cid=c.cid AND c.cid=? ";
        String sql="SELECT p.* FROM product p JOIN category c , categorysecond cs WHERE p.csid=cs.csid AND cs.cid=c.cid AND c.cid=? limit ?, ?";
        try {
            Number num= (Number) runner.query(sqlCount,new ScalarHandler(),cid);
            bean.setCount(num.intValue());
            List<Product> list=runner.query(sql,new BeanListHandler<Product>(Product.class),cid,(page-1)*pageSize,pageSize);
            bean.setPageList(list);
            return bean;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public PageBean<Product> findPageBeanByCsid(int page, int pageSize, int csid) {
        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource());
        PageBean<Product> bean=new PageBean<Product>();
        bean.setPage(page);
        bean.setPageSize(pageSize);
        String sqlCount="SELECT count(*) FROM product p JOIN  categorysecond cs WHERE p.csid=cs.csid AND cs.csid=? ";
        String sql="SELECT p.* FROM product p JOIN  categorysecond cs WHERE p.csid=cs.csid AND cs.csid=? limit ?, ?";
        try {
            Number num1= (Number) runner.query(sqlCount,new ScalarHandler(),csid);
            bean.setCount(num1.intValue());
            List<Product> list=runner.query(sql,new BeanListHandler<Product>(Product.class),csid,(page-1)*pageSize,pageSize);
            bean.setPageList(list);
            return bean;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List findNewProduct(int size) {
        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource());
        String sql = "select * from product order by pdate desc limit 0,?";
        List list =null;
        try {
            list = (List) runner.query(sql,new BeanListHandler(Product.class),size);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public Product fingByProductPid(int pid) {
        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource());
        String sql = "select * from product where pid=?";
        try {
            Product product= (Product) runner.query(sql,new BeanHandler<Product>(Product.class),pid);
            return product;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    public List<Evaluate> findEvaluateByPid(int pid){
        QueryRunner runner=new QueryRunner(C3P0Utils.getDataSource());
        String sql="SELECT * FROM evaluate e JOIN product p, USER u WHERE e.pid=p.pid AND e.uid=u.uid AND p.pid=?";
        List<Evaluate> evaluateList=new ArrayList<Evaluate>();
        List<Map<String,Object>> list=new ArrayList<Map<String, Object>>();
        try {
            list=runner.query(sql,new MapListHandler(),pid);
            for (Map<String,Object> m:list) {
                User user=new User();
                Product product=new Product();
                Evaluate evaluate=new Evaluate();
                BeanUtils.populate(user,m);
                BeanUtils.populate(product,m);
                BeanUtils.populate(evaluate,m);
                evaluate.setUser(user);
                evaluate.setProduct(product);
                evaluateList.add(evaluate);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return evaluateList;
    }
}
